# -*- coding: utf-8 -*-
import openpyxl


class ParseExcel:
    # 初始化表格
    def __init__(self, excel_file_path=None):
        self.excel_file_path = excel_file_path
        if self.excel_file_path:
            self.wb = openpyxl.load_workbook(self.excel_file_path)
        else:
            self.wb = openpyxl.Workbook()
        self.ws = self.wb.active

    # 通过sheet名获取sheet表
    def get_sheet_by_name(self, sheet_name):
        self.ws = self.wb.get_sheet_by_name(sheet_name)
        return self.ws

    # 通过索引获取sheet表
    def get_sheet_by_index(self, index):
        sheet_name = self.wb.sheetnames[index - 1]
        self.ws = self.wb[sheet_name]
        return self.ws

    def write_rows_data(self, dataList, save_file_path=None):
        """
        写入多行数据
        :param dataList: 需要写入的数据[(第一行数据)，（第二行数据）.....  ]
        :param save_file_path:excel文件不存在才需要指定保存路径，存在不需要指定
        """
        for data in dataList:
            # 如果内容是字典的话只填入value
            if isinstance(data, dict):
                new_data = []
                for value in data.values():
                    # 只有字符串类型才能写入
                    if type(value) is not str:
                        value = str(value)
                    new_data.append(value)
                data = new_data
            self.ws.append(data)
        if save_file_path:
            self.wb.save(save_file_path)
        else:
            self.wb.save(self.excel_file_path)
        self.wb.close()

    def get_col_data(self, col, row=1):
        """
        获取一列数据
        :param col: 列
        :param row: 从第几行开始，默认从1开始
        :return:一列数据
        """
        cols_data = self.ws.iter_cols(min_col=col, max_col=col, min_row=row)
        res = []
        for col_data in cols_data:
            for cell in col_data:
                res.append(cell.value)
        return res

    def read_rows_data(self, min_row=None, max_row=None, min_col=None, max_col=None):
        """
        读行数据
        :param min_row: 最小行号
        :param max_row: 最大行号
        :param min_col: 最小列号
        :param max_col: 最大列号
        :return:指定的行数据
        """
        result = []
        for row in self.ws.iter_rows(min_row, max_row, min_col, max_col,values_only=True):
            result.append(row)
        return result


if __name__ == '__main__':
    from tools.handle_path import report_path

    filepath = f"{report_path}/test_export_dimension_member.xlsx"
    r = ParseExcel(filepath)
    r = r.read_rows_data(min_row=2)
    print(r)
